package com.common;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by 10742 on 2017/7/8.
 */
public class ColonyTool extends JdbcDaoSupport {

    public int insert(final String sampleID, final int sampleNum, final String description, final String dType, final int numSimple, final int totalNum, final String diversitySimple, final String diversityNucle, final int numAllele, final String observeHeter, final String expectHeter) {
        final String sql = "insert into Colony_ResearData(SampleNum, Description, Dtype, NumSimple, TotalNum, DiversitySimple, DiversityNucle, NumAllele, ObserHeter, ExpectHeter, FishClassInfom_ID) values(?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, sampleNum);
                ps.setString(2, description);
                ps.setString(3, dType);
                ps.setInt(4, numSimple);
                ps.setInt(5, totalNum);
                ps.setString(6, diversitySimple);
                ps.setString(7, diversityNucle);
                ps.setInt(8, numAllele);
                ps.setString(9, observeHeter);
                ps.setString(10, expectHeter);
                ps.setString(11, sampleID);
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public int update(int colonyID, int sampleNum, String description, String dType, int numSimple, int totalNum, String diversitySimple, String diversityNucle, int numAllele, String observeHeter, String expectHeter){
        String sql = "update Colony_ResearData set SampleNum=?,Description=?,Dtype=?,NumSimple=?,TotalNum=?,DiversitySimple=?,DiversityNucle=?,NumAllele=?,ObserHeter=?,ExpectHeter=? where ID=?";
        return getJdbcTemplate().update(sql, sampleNum, description, dType, numSimple, totalNum, diversitySimple, diversityNucle, numAllele, observeHeter, expectHeter, colonyID);
    }
}
